package com.ysstech.reportworld.service.impl;

import com.ysstech.common.exception.BusinessException;
import com.ysstech.reportworld.entity.TableCheck;
import com.ysstech.reportworld.enums.ReportWorldEnum;
import com.ysstech.reportworld.mapper.TableCheckMapper;
import com.ysstech.reportworld.service.TableCheckService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
 * <p>
 * 标准层表校验语句信息 服务实现类
 * </p>
 *
 * @author lishuangliang
 * @since 2022-05-19
 */
@Service
public class TableCheckServiceImpl extends ServiceImpl<TableCheckMapper, TableCheck> implements TableCheckService {
    /**
     * 检查表结构信息中是否都有所属模块，客户和报表信息
     *
     * @return
     */
    public String checkTableAndDataSqlInfo(Connection connection) throws Exception {
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        StringBuffer msgInfo = new StringBuffer();
        try {
            Map<String, String> map = new HashMap<>();
            map.put("REPORT_WORLD_TABLE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
            // map.put("REPORT_WORLD_DATASQL", ReportWorldEnum.REPORT_DATASQL_CODE.getCode());
            for (Map.Entry<String, String> entry : map.entrySet()) {
                String sql =
                        "SELECT * FROM  " + entry.getKey() + " A WHERE  A.STATUS = 1" +
                                "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_MODULAR B  " +
                                " WHERE B.DATA_ID = A.ID  AND B.DATA_CODE = '" + entry.getValue() + "')" +
                                "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_REPORT C " +
                                " WHERE C.DATA_ID = A.ID  AND C.DATA_CODE = '" + entry.getValue() + "')" +
                                "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_CUSTOMER D " +
                                " WHERE D.DATA_ID = A.ID  AND D.DATA_CODE = '" + entry.getValue() + "')";
                // 查询
                pStemtSelect = connection.prepareStatement(sql);
                rs = pStemtSelect.executeQuery();
                StringBuffer buffer = new StringBuffer();
                while (rs.next()) {
                    buffer.append("\r\n");
                    buffer.append("【").append(rs.getString("NAME")).append("】");
                }
                rs.close();
                if (buffer.length() > 0) {
                    msgInfo.append("警告：表名【" + entry.getKey() + "】").append(buffer).append("，校验不通过原因：在系统中没有对应所属模块，所属客户，所属报表信息，请检查！").append("\r\n");
                }
            }
            StringBuffer buffer = new StringBuffer();
            String sql =
                    " SELECT * FROM REPORT_WORLD_TABLE_DETAIL A WHERE" +
                            " NOT EXISTS (SELECT * FROM RW_DB_TP_GL_EXHIB_DATADICT B WHERE A.CODE_NAME = B.CLASS_ID AND B.RW_DB_STATUS = 1)" +
                            " AND A.STATUS = 1 AND A.CODE_NAME IS NOT NULL " +
                            " ORDER BY A.CODE_NAME DESC";
            // 查询
            pStemtSelect = connection.prepareStatement(sql);
            rs = pStemtSelect.executeQuery();
            if (buffer.length() > 0) {
                msgInfo.append("警告：表明细信息配置的字典值").append(buffer).append("，校验不通过原因：在系统中没有对应字典信息，请检查！").append("\r\n");
            }
            rs.close();
        } catch (Exception ex) {
            throw new BusinessException("检查表数据信息出错：" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemtSelect != null) {   // 关闭声明
                pStemtSelect.close();
            }
        }
        return msgInfo.toString();
    }
}
